﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CNCSoft.ErpApps.TMS.myclass;
using Common;

namespace CNCSoft.ErpApps.TMS.longGiaoVu
{
    public partial class frmTinhLuongGiaoVien : Form
    {
        private SqlConnection conn;
        private DataSet ds;
        private CommonClass mine;
        int thang;
        private string companyID;
        private bool signData = false;
        private bool signKhoaHoc = false;
        private bool signRate = false;
        private bool signTongGioDay = false;
        private bool signTongRate = false;
        private bool signGiaoVienThanhToan = false;

        public frmTinhLuongGiaoVien()
        {
            InitializeComponent();
        }

        private void frmTinhLuongGiaoVien_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
            ds = new DataSet();
            mine = new CommonClass();
            companyID = GlobalVariable.WSInfo.CompanyID;

            LoadControl();
        }

        private void searchLookUpEditGiaoVien_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                if (textEditThang.Text == "")
                {
                    MessageBox.Show("Xin mời bạn nhập tháng.");
                    return;
                }
                else
                {
                    thang = Convert.ToInt32(textEditThang.Text);

                    searchLookUpEditClass.Text = "";
                    textEditRate.Text = "";
                    textEditLuongMotRate.Text = "";
                    lblTongRate.Text = "";
                    lblTongLuong.Text = "";
                    lblTenGiaoVien.Text = "";
                    lblSoGioDay.Text = "";
                    string magiaovien = searchLookUpEditGiaoVien.EditValue.ToString();
                    LoadData(magiaovien, thang);
                    LoadKhoaHocByGiaoVien(magiaovien, thang);
                    lblTenGiaoVien.Text = searchLookUpEditGiaoVien.Text;
                    lblSoGioDay.Text = LoadTongSoGioDayByGiaoVien(magiaovien, thang);
                    lblTongRate.Text = LoadRateByGiaoVien(magiaovien);

                    if (CheckGiaoVienDaThanhToan(magiaovien))
                    {
                        btnThanhToan.Enabled = false;
                    }
                    else
                    {
                        btnThanhToan.Enabled = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void searchLookUpEditClass_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                string makhoahoc = searchLookUpEditClass.EditValue.ToString();
                lblLop.Text = searchLookUpEditClass.Text;                
                textEditRate.Text = LoadRateByKhoaHoc(makhoahoc);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnCapNhatRate_Click(object sender, EventArgs e)
        {
            try
            {
                if (textEditRate.Text == "")
                {
                    MessageBox.Show("Xin mời bạn nhập Rate");
                }
                else
                {
                    string makhoahoc = searchLookUpEditClass.EditValue.ToString();
                    Double rate = Convert.ToDouble(textEditRate.Text);
                    UpdateRate(makhoahoc, rate);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnTienLuong_Click(object sender, EventArgs e)
        {
            try
            {
                CalTienLuong();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnThanhToan_Click(object sender, EventArgs e)
        {
            try
            {
                if (lblTongLuong.Text != "" && lblTongLuong.Text != "- - -")
                {
                    string magiaovien = searchLookUpEditGiaoVien.EditValue.ToString();
                    int thang = Convert.ToInt32(textEditThang.Text);
                    double tongrate = Convert.ToDouble(lblTongRate.Text);
                    double luong = Convert.ToDouble(lblTongLuong.Text);
                    double tonggioday = Convert.ToDouble(lblSoGioDay.Text);
                    ThanhToan(thang, tongrate, luong, magiaovien, tonggioday);
                }
                else
                {
                    MessageBox.Show("Xin bạn vui lòng tính ra tiền lương");
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnLamLai_Click(object sender, EventArgs e)
        {
            try
            {
                Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region coding
        private void LoadControl()
        {
            try
            {
                LoadGiaoVien();
                lblNgay.Text = String.Format("{0:d/M/yyyy}", DateTime.Now);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadGiaoVien()
        {
            try
            {
                string sql =
                    @"SELECT
                        MaGiaoVien, TenGiaoVien
                        FROM DM_GIAOVIEN
                        WHERE MaDonVi = '" + companyID + "'";
                mine.ExecuteDataAdapter(sql, conn, ds, "TableGiaoVien", "MaGiaoVien", "TenGiaoVien", searchLookUpEditGiaoVien);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadData(string magiaovien, int thang)
        {
            try 
	        {
                if (signData)
                {
                    ds.Tables["TableData"].Rows.Clear();
                }
		        string sql =
                @"SELECT DISTINCT
                    dmkh.TenKhoaHoc AS N'Tên lớp', khgv.MaGiaoVien AS 'Giáo Viên Lớp', 
                    qtht.NgayHoc AS N'Ngày dạy', qtht.SoGioDay AS N'Số giờ dạy',
                    qtht.MaGiaoVien AS N'Giáo viên dạy'
                    FROM Invoice_KhoaHoc_QTHT qtht, Invoice_KhoaHoc invkh, DM_KHOAHOC dmkh, KhoaHoc_GiaoVien khgv
					WHERE qtht.MaHocTap = invkh.ID AND dmkh.MaKhoaHoc = invkh.MaKhoaHoc AND dmkh.MaKhoaHoc = khgv.MaKhoaHoc
                    AND qtht.MaGiaoVien = '" + magiaovien + @"'
                    AND DATEPART(MONTH, qtht.NgayHoc) = " + thang;
                mine.ExecDataAdapter(sql, conn, ds, "TableData", gridControlData);
                signData = true;
	        }
	        catch (Exception ex)
	        {
                MessageBox.Show(ex.Message);
	        }
        }

        private void LoadKhoaHocByGiaoVien(string magiaovien, int thang)
        {
            try
            {
                if (signKhoaHoc)
                {
                    ds.Tables["TableKhoaHoc"].Rows.Clear();
                }
                string sql =
                @"SELECT MaKhoaHoc, TenKhoaHoc
                    FROM DM_KHOAHOC
                    WHERE MaKhoaHoc IN
                    (
                        SELECT DISTINCT
                        dmkh.MaKhoaHoc
                        FROM
                        DM_KHOAHOC dmkh, Invoice_KhoaHoc invkh, Invoice_KhoaHoc_QTHT qtht, KhoaHoc_GiaoVien khgv
						WHERE dmkh.MaKhoaHoc = invkh.MaKhoaHoc AND qtht.MaHocTap = invkh.ID
                        AND khgv.MaGiaoVien = '" + magiaovien + @"'
                        AND DATEPART(MONTH, qtht.NgayHoc) = " + thang + @"
                    )";
                mine.ExecuteDataAdapter(sql, conn, ds, "TableKhoaHoc", "MaKhoaHoc", "TenKhoaHoc", searchLookUpEditClass);
                signKhoaHoc = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private string LoadRateByGiaoVien(string magiaovien)
        {
            try
            {
                if (signTongRate)
                    ds.Tables["Rate"].Rows.Clear();
                string sql =
                    @"SELECT SUM(CASE WHEN RateUSD = 0 THEN RateVND ELSE RateUSD END)
                        FROM KhoaHoc_GiaoVien
                        WHERE ID IN
                        (
	                        SELECT
                                qtht.MaHocTap
                                FROM
                                Invoice_KhoaHoc_QTHT qtht, KhoaHoc_GiaoVien khgv
																WHERE qtht.MaHocTap = khgv.ID
                                                                AND khgv.MaGiaoVien = '" + magiaovien + @"'
                                                                AND DATEPART(MONTH, qtht.NgayHoc) = " + thang + @"
												)";
//                    @"SELECT SUM(Rate)
//                        FROM DM_KHOAHOC
//                        WHERE MaKhoaHoc IN
//                        (
//	                        SELECT DISTINCT
//                                dmkh.MaKhoaHoc
//                                FROM
//                                DM_KHOAHOC dmkh
//                                INNER JOIN Invoice_KhoaHoc invkh ON dmkh.MaKhoaHoc = invkh.MaKhoaHoc
//                                INNER JOIN Invoice_KhoaHoc_QTHT qtht ON qtht.MaHocTap = invkh.ID
//                                INNER JOIN DM_GIAOVIEN dmgv ON dmgv.MaGiaoVien = qtht.MaGiaoVien
//                                WHERE dmgv.MaGiaoVien = '" + magiaovien + @"'
//                                AND DATEPART(MONTH, qtht.NgayHoc) = " + thang + @"
//                        )";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "Rate");
                signTongRate = true;
                return ds.Tables["Rate"].Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private string LoadTongSoGioDayByGiaoVien(string magiaovien, int thang)
        {
            try
            {
                if (signTongGioDay)
                    ds.Tables["TongGioDay"].Rows.Clear();
                string sql =
                    @"SELECT SUM(t.SoGioDay)
                        FROM 
                        (
	                        SELECT DISTINCT
	                        NgayHoc, SoGioDay
	                        FROM Invoice_KhoaHoc_QTHT
	                        WHERE MaGiaoVien = '" + magiaovien + @"'
                            AND DATEPART(MONTH, NgayHoc) = " + thang + @"
                        )
                        AS t";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TongGioDay");
                signTongGioDay = true;
                return ds.Tables["TongGioDay"].Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private string LoadRateByKhoaHoc(string makhoahoc)
        {
            try
            {
                if (makhoahoc != "")
                {
                    if (signRate)
                        ds.Tables["TongRate"].Rows.Clear();
                    string sql =
                        @"SELECT
	                    Rate
	                    FROM KhoaHoc_GiaoVien khgv, DM_KHOAHOC dmkh
						where khgv.MaKhoaHoc = dmkh.MaKhoaHoc
						and khgv.MaKhoaHoc = '" + makhoahoc + @"'";
                    SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                    da.Fill(ds, "TongRate");
                    signRate = true;
                    return ds.Tables["TongRate"].Rows[0][0].ToString();
                }
                return "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private void UpdateRate(string makhoahoc, double rate)
        {
            try
            {
                int i = 0;
                string sql =
                        @"UPDATE DM_KHOAHOC SET Rate = @rate WHERE MaKhoaHoc = @makhoahoc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@rate", rate);
                cmd.Parameters.AddWithValue("@makhoahoc", makhoahoc);
                try
                {
                    conn.Open();
                    i = cmd.ExecuteNonQuery();
                }
                finally
                {
                    if (conn != null)
                        conn.Close();
                }
                if (i > 0)
                {
                    MessageBox.Show("Update rate cho lớp thành công");
                    lblTongRate.Text = LoadRateByGiaoVien(searchLookUpEditGiaoVien.EditValue.ToString());
                }
                else
                {
                    MessageBox.Show("Thất bại");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void CalTienLuong()
        {
            try
            {
                lblTongLuong.Text = (Convert.ToDouble(lblSoGioDay.Text) * Convert.ToDouble(lblTongRate.Text) * Convert.ToDouble(textEditLuongMotRate.Text)).ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ThanhToan(int thang, double tongrate, double luong, string magiaovien, double tonggioday)
        {
            try
            {
                if (lblTongLuong.Text != "- - -" && lblTongLuong.Text != "")
                {
                    int i = 0;
                    string sql =
                        @"INSERT INTO Luong_GiaoVien(MaGiaoVien, Thang, TongRate, TongGioDay, Luong) VALUES(@magiaovien, @thang, @tongrate,@tonggioday, @luong)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@thang", thang);
                    cmd.Parameters.AddWithValue("@tongrate", tongrate);
                    cmd.Parameters.AddWithValue("@luong", luong);
                    cmd.Parameters.AddWithValue("@magiaovien", magiaovien);
                    cmd.Parameters.AddWithValue("@tonggioday", tonggioday);
                    try
                    {
                        conn.Open();
                        i = cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (conn != null)
                            conn.Close();
                    }
                    if (i > 0)
                        MessageBox.Show("Thanh toán thành công");
                    else
                        MessageBox.Show("Thanh toán thất bại");
                }
                else
                {
                    MessageBox.Show("Bạn chưa tính ra tổng lương của giáo viên này");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private bool CheckGiaoVienDaThanhToan(string magiaovien)
        {
            try
            {
                if (signGiaoVienThanhToan)
                    ds.Tables["CheckGiaoVienThanhToan"].Rows.Clear();
                string sql =
                    @"SELECT ID FROM Luong_GiaoVien WHERE MaGiaoVien = '" + magiaovien + "' AND Thang = " + thang;
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "CheckGiaoVienThanhToan");
                signGiaoVienThanhToan = true;
                if (ds.Tables["CheckGiaoVienThanhToan"].Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        private void Refresh()
        {
            try
            {
                lblTongLuong.Text = "";
                textEditRate.Text = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}